package com.commons.sql;

import java.lang.reflect.Field;
import java.util.List;
import java.util.Set;

import com.commons.annotation.Id;
import com.commons.exception.DeleteSqlException;
import com.commons.exception.NoIdException;

public class SqlUtil {
	
	public static final int INSERT=1;
	public static final int REPLACE=2;
	public static final int DELETE=3;
	public static final int DELETE_ID=4;
	public static final int SELECT=5;
	public static final int SELECT_ID=6;
	
	
	
	public static String generateInsertSql(Class clazz) {
		boolean hasID = false;
		
		StringBuffer sql = new StringBuffer();

		sql.append("insert into ");
		sql.append(clazz.getSimpleName());
		sql.append("(");

		Field[] fields = clazz.getDeclaredFields();

		for (Field field : fields) {
			Id id = field.getAnnotation(Id.class);
			if (id != null) {
				hasID=true;
				continue;
			} else {
				sql.append(field.getName());
				sql.append(",");
			}
		}
		
		if(!hasID)throw new NoIdException("java bean has no id");

		sql = new StringBuffer(sql.substring(0, sql.length() - 1));
		sql.append(") values(");

		for (Field field : fields) {
			Id id = field.getAnnotation(Id.class);
			if (id != null)
				continue;
			else {
				sql.append(":");
				sql.append(field.getName());
				sql.append(",");
			}
		}
		
		sql = new StringBuffer(sql.substring(0, sql.length() - 1));
		sql.append(")");
		return sql.toString();
	}
	
	public static String generateReplaceSql(Class clazz)
	{
		StringBuffer sql = new StringBuffer();

		sql.append("replace into ");
		sql.append(clazz.getSimpleName());
		sql.append("(");

		Field[] fields = clazz.getDeclaredFields();

		for (Field field : fields) {
				sql.append(field.getName());
				sql.append(",");	
		}
		sql = new StringBuffer(sql.substring(0, sql.length() - 1));
		sql.append(") values(");

		for (Field field : fields) {
			
				sql.append(":");
				sql.append(field.getName());
				sql.append(",");
		}
		
		sql = new StringBuffer(sql.substring(0, sql.length() - 1));
		sql.append(")");
		return sql.toString();
	}
	
	public static String generateDeleteSql(Object obj,List<SearchCondition> params)
	{
		
		StringBuffer sql = new StringBuffer();
		sql.append("delete from ");
		sql.append(obj.getClass().getSimpleName());
		sql.append(" where 1=1");
		
		if(params==null)throw new DeleteSqlException("delete sql should have params");
		
		for(SearchCondition condition:params)
		{
			sql.append(" and ");
			sql.append(condition.getKey());
			sql.append(" ");
			sql.append(condition.getOperator());
			sql.append(" :");
			sql.append(condition.getAlias());
		}
		return sql.toString();
	}
	
	public static String generateDeleteSqlById(Class clazz) 
	{
		StringBuffer sql = new StringBuffer();
		sql.append("delete from ");
		sql.append(clazz.getSimpleName());
		sql.append(" where 1=1");
		
		Field[] fields = clazz.getDeclaredFields();

		boolean hasID=false;
		
		for (Field field : fields) {
			Id id = field.getAnnotation(Id.class);
			if (id != null) {
				sql.append(" and ");
				sql.append(field.getName());
				sql.append("= ?");
				hasID=true;
			} 
		}
		
		if(!hasID)throw new NoIdException("java bean has no id");
		return sql.toString();
	}
	
	
	public static String generateSelectSql(Class clazz,List<SearchCondition> params)
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
		sql.append(clazz.getSimpleName());
		sql.append(" where 1=1");
		
		if(params==null || params.size()==0)return sql.toString();
		
		for(SearchCondition param:params)
		{
			sql.append(" and ");
			sql.append(param.getKey());
			sql.append(" = :");
			sql.append(param.getAlias());
			
		}
		return sql.toString();
	}
	
	
	public static String generateSelectSqlById(Class clazz)
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ");
		sql.append(clazz.getSimpleName());
		sql.append(" where 1=1");
		
		Field[] fields = clazz.getDeclaredFields();

		boolean hasID=false;
		
		for (Field field : fields) {
			Id id = field.getAnnotation(Id.class);
			if (id != null) {
				sql.append(" and ");
				sql.append(field.getName());
				sql.append("= ?");
				hasID=true;
			} 
		}
		
		if(!hasID)throw new NoIdException("java bean has no id");
		return sql.toString();
	}
}
